﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using Jenzabar.Portal.Framework.Web.UI;
using Jenzabar.Portal.Framework;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Drawing;
namespace Portlet.AURoster
{
    public partial class Reg_View : PortletViewBase
    {
        string cid, exportSql;
        DataTable exportTable = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsFirstLoad)
            {
                pnlGV.Visible = false;
                btnExport.Visible = false;
                PopulateCoach(ddCoach);
                ddCoach.Items.Add(new ListItem("", ""));
                ddCoach.SelectedValue = "";
              
            }
        }

        private void PopulateCoach(DropDownList dd)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SELECT [Coach_ID],[Coach] FROM [TmsEprd].[dbo].[CUS_AURoster_Coach]  ", myConn);
                //cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader rdr;

                try
                {
                    myConn.Open();
                    rdr = cmd.ExecuteReader();
                    dd.DataSource = rdr;
                    dd.DataValueField = "Coach_ID";
                    dd.DataTextField = "Coach";
                    dd.DataBind();
                    rdr.Close();

                }
                catch (Exception ex)
                {
                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error retrieving Data. " + ex.Message);


                }
                finally
                {

                    myConn.Close();
                }
            }
        }

        private void BindGV(GridView gv, string coach)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    cid = ddCoach.SelectedValue;
                    Session["cid"] = cid;
                    myConn.Open();
                    string exportHeader = "Select Team = ad.ATTRIBUTE_DESC,  Coach =  cac.[Coach]  ";
                    exportHeader += " from [TmsEprd].[dbo].[CUS_AURoster_Team] cat ";
                    exportHeader += " inner join TmsEprd.dbo.CUS_AURoster_Coach  cac on cac.Coach_ID = cat.Coach_ID ";
                    exportHeader += " inner join TmsEprd.dbo.ATTRIBUTE_DEF ad on ad.ATTRIBUTE_CDE = cac.Team ";
                    exportHeader += "WHERE cac.[Coach_ID] ='" + cid + "'";

                    SqlCommand cmd = new SqlCommand("Select Student_ID,  Last_Name,  First_Name, No_of_Competed, NAIA_EC#,HS_OR_College,HS_GRAD_DATE ,  [Status]  from [TmsEprd].[dbo].[CUS_AURoster_Team] WHERE [Coach_ID] ='" + coach + "' Order by Last_Name", myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    //DataTable dt = new DataTable();

                    DataSet ds = new DataSet();

                    da.Fill(ds, "DataView");
                    Session["myTable"] = ds;
                    gv.DataSource = ds;
                    gv.DataBind();

                    SqlCommand hdr = new SqlCommand(exportHeader, myConn);
                     SqlDataReader rdr;
                    rdr = hdr.ExecuteReader();
                    while (rdr.Read())
                    {
                        lblTeam.Text = rdr["Team"].ToString().ToUpper() + " ";
                    }

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Binding Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            BindGV(gvPlayer, ddCoach.SelectedValue);
            btnExport.Visible = true;
            pnlGV.Visible = true;
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            cid = ddCoach.SelectedValue ;
            string header;// = "Coach : " + PortalUser.Current.FirstName +" " + PortalUser.Current.LastName ;

            string exportHeader = "Select Team = ad.ATTRIBUTE_DESC,  Coach =  cac.[Coach]   ";
            exportHeader += " from [TmsEprd].[dbo].[CUS_AURoster_Team] cat ";
            exportHeader += " inner join TmsEprd.dbo.CUS_AURoster_Coach  cac on cac.Coach_ID = cat.Coach_ID ";
            exportHeader += " inner join TmsEprd.dbo.ATTRIBUTE_DEF ad on ad.ATTRIBUTE_CDE = cac.Team ";
            exportHeader += "WHERE cac.[Coach_ID] ='" + cid + "'";

            exportSql = "Select  Last_Name , First_Name, SSN, [Status], No_of_Competed, NAIA_EC#, HS_OR_College,HS_GRAD_DATE , cat.[TIMESTAMP] ";
            exportSql += " from [TmsEprd].[dbo].[CUS_AURoster_Team] cat ";
            exportSql += " inner join TmsEprd.dbo.CUS_AURoster_Coach  cac on cac.Coach_ID = cat.Coach_ID ";
            exportSql += " inner join TmsEprd.dbo.ATTRIBUTE_DEF ad on ad.ATTRIBUTE_CDE = cac.Team ";
            exportSql += "WHERE cac.[Coach_ID] ='" + cid + "'" + " Order by Last_Name " ;






            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {
                try
                {
                    myConn.Open();
                    SqlCommand hdr = new SqlCommand(exportHeader, myConn);



                    SqlDataAdapter da = new SqlDataAdapter(exportSql, myConn);
                    da.Fill(exportTable);
                    string attachment = "attachment; filename=" + cid + " AURoster.xls";

                    Response.ClearContent();

                    Response.AddHeader("content-disposition", attachment);

                    Response.ContentType = "application/vnd.ms-excel";
                    SqlDataReader rdr;
                    rdr = hdr.ExecuteReader();
                    while (rdr.Read())
                    {

                        header = "Coach : " + rdr["Coach"].ToString() + "   " + "Team :" + rdr["Team"].ToString();
                        //lblTeam.Text = rdr["Team"].ToString() + " ";
                        Response.Write(header);
                        Response.Write("\n");


                        string tab = "";

                        foreach (DataColumn dc in exportTable.Columns)
                        {

                            Response.Write(tab + dc.ColumnName);

                            tab = "\t";

                        }

                        Response.Write("\n");



                        int i;

                        foreach (DataRow dr in exportTable.Rows)
                        {

                            tab = "";

                            for (i = 0; i < exportTable.Columns.Count; i++)
                            {

                                Response.Write(tab + dr[i].ToString());

                                tab = "\t";

                            }

                            Response.Write("\n");

                        }

                        Response.End();
                    }
                    rdr.Close();

                }
                catch (Exception ex)
                {

                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Exporting Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        protected void gvPlayer_SelectedIndexChanged(object sender, EventArgs e)
        {
            dv.Visible = true;
            //dv to edit Mode
            dv.ChangeMode(DetailsViewMode.Edit);
            populateDV();
        }

        private void populateDV()
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();
                    //GridViewRow selectedRow = gvPlayer.SelectedRow;
                    int selectedIndex = gvPlayer.SelectedIndex;
                    //Read the ID From the row
                    string id = (gvPlayer.DataKeys[selectedIndex].Value).ToString();

                    // selectedRowSID = selectedRow.Cells[0].Text;
                    SqlCommand cmd = new SqlCommand("SELECT * FROM [TmsEprd].[dbo].[CUS_AURoster_Team] WHERE Student_ID = '" + id + "'", myConn);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    DataSet ds = new DataSet();

                    da.Fill(ds, "DataView");

                    //SqlDataReader reader;
                    //reader = cmd.ExecuteReader();
                    dv.DataSource = ds;
                    dv.DataBind();
                }
                catch (Exception ex)
                {
                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error retrieving Data. test " + ex.Message);
                    // this.ParentPortlet.NextScreen("Default");

                }
                finally
                {

                    myConn.Close();
                }

            }
        }

        protected void dv_DataBound(object sender, EventArgs e)
        {
            if (dv.CurrentMode == DetailsViewMode.Edit)
            {
                //Added for dropdown selected value( actually this is already submitted value)
                //For that add the TextBox or Label in the detail view check Edi_view.ascx make it visible false
                TextBox status = (TextBox)dv.FindControl("txtStatus");
                DropDownList dd = (DropDownList)dv.FindControl("ddStatus");
                if (dd != null)
                {
                    List<lsStatus> lstStr = new List<lsStatus>();

                    lstStr.Add(new lsStatus("New FR", "New FR"));
                    lstStr.Add(new lsStatus("New TR", "New TR"));
                    lstStr.Add(new lsStatus("Return", "Return"));
                    lstStr.Add(new lsStatus("Delete", "Delete"));

                    dd.DataSource = lstStr;
                    dd.DataValueField = "StatusCode";
                    dd.DataTextField = "StatusValue";
                    dd.DataBind();
                }
                //dropdown populated already existed data for this add the text control in DV(dv) make it visible false
                dd.SelectedValue = status.Text.Trim();
            }
        }
        protected void dv_ItemCreated(object sender, EventArgs e)
        {
            if (dv.CurrentMode == DetailsViewMode.Edit)
            {
                DropDownList dd = (DropDownList)dv.FindControl("ddStatus");
                if (dd != null)
                {
                    List<lsStatus> lstStr = new List<lsStatus>();

                    lstStr.Add(new lsStatus("New FR", "New FR"));
                    lstStr.Add(new lsStatus("New TR", "New TR"));
                    lstStr.Add(new lsStatus("Return", "Return"));
                    lstStr.Add(new lsStatus("Delete", "Delete"));

                    dd.DataSource = lstStr;
                    dd.DataValueField = "StatusCode";
                    dd.DataTextField = "StatusValue";
                    dd.DataBind();
                }
            }
        }


        public class lsStatus
        {
            public string _sCode;
            public string _sValue;

            public lsStatus(string sCode, string sValue)
            {
                this._sCode = sCode;
                this._sValue = sValue;

            }
            public string StatusCode
            {
                get { return _sCode; }
                set { _sCode = value; }

            }

            public string StatusValue
            {
                get { return _sValue; }
                set { _sValue = value; }

            }

        }

        protected void dv_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
        {
            int selectedIndex = gvPlayer.SelectedIndex;
            //Read the ID From the row
            string SID = (gvPlayer.DataKeys[selectedIndex].Value).ToString();
            TextBox fname = (TextBox)dv.FindControl("first");
            TextBox lname = (TextBox)dv.FindControl("Last");
            DropDownList ddstatus = (DropDownList)dv.FindControl("ddStatus");
            TextBox compete = (TextBox)dv.FindControl("txtCompete");
            TextBox naia = (TextBox)dv.FindControl("txtEC");
            TextBox hs = (TextBox)dv.FindControl("txtHS");
            TextBox gdate = (TextBox)dv.FindControl("txtDate");
            UpdateRecord(SID, fname.Text, lname.Text, ddstatus.SelectedValue.ToString(), compete.Text, naia.Text, hs.Text, gdate.Text);
            //Added this time stamp on March 28 2014
            string dt;
            dt = DateTime.Now.ToString();
            UpdateTimeStamp(dt);

            dv.Visible = false;

            ddCoach.SelectedValue = Session["cid"].ToString();
            BindGV(gvPlayer, Session["cid"].ToString());
        }

        private void UpdateTimeStamp(string dt)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    SqlCommand cmd = new SqlCommand("CUS_sp_AURoster_Coach_Update", myConn);
                    cmd.Parameters.Add("@cid", SqlDbType.VarChar, 10).Value = PortalUser.Current.HostID;
                    cmd.Parameters.Add("@timestamp", SqlDbType.VarChar, 10).Value = dt;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Inserting Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }

        private void UpdateRecord(string SID, string fname, string lname, string status, string compete, string naia, string hs, string gdate)//, string dd, string cb)
        {
            using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NotepadConnectionString"].ConnectionString))
            {

                try
                {
                    myConn.Open();

                    SqlCommand cmd = new SqlCommand("CUS_sp_AURoster_Player_Update", myConn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@first", SqlDbType.VarChar).Value = fname;
                    cmd.Parameters.Add("@last", SqlDbType.VarChar).Value = lname;
                    cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
                    cmd.Parameters.Add("@sid", SqlDbType.VarChar).Value = SID;
                    cmd.Parameters.Add("@compete", SqlDbType.VarChar, 100).Value = Convert.ToInt32(compete);
                    cmd.Parameters.Add("@ec#", SqlDbType.VarChar).Value = naia;
                    cmd.Parameters.Add("@hs", SqlDbType.VarChar).Value = hs;
                    cmd.Parameters.Add("@gdate", SqlDbType.VarChar).Value = gdate;
                    cmd.ExecuteNonQuery();


                }
                catch (Exception ex)
                {


                    this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Error Updating Data Items: " + ex.Message);
                }
                finally
                {
                    // Close Connection
                    myConn.Close();
                }
            }
        }


        protected void dv_ModeChanging(object sender, DetailsViewModeEventArgs e)
        {
            dv.ChangeMode(e.NewMode);
            populateDV();
            if (e.NewMode == DetailsViewMode.ReadOnly)
            {
                if (e.CancelingEdit || e.Cancel)
                {
                    dv.Visible = false;

                }
            }
        }
        //Added for edit on 9/26/2014
        protected void ddCoach_SelectedIndexChanged(object sender, EventArgs e)
        {
            gvPlayer.Visible = false;
            dv.Visible = false; //added on 10/30/2014
            string coach = ddCoach.SelectedValue;
            if (coach != "")
            {
                gvPlayer.Visible = true;
                Session["coach"] = coach;
                BindGV(gvPlayer, coach);
            }
        }

        protected void gvPlayer_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // For Paging 
            gvPlayer.Visible = true;
            gvPlayer.PageIndex = e.NewPageIndex;

            string coach = ddCoach.SelectedValue;
            BindGV(gvPlayer, coach);
        }
    }
}